Alter proc wip_info_asofdate_Schedule_spectra
as
begin

	DECLARE @LOT_ID 	BIGINT
    	DECLARE @DATE_TIME 	DATETIME
    	DECLARE @START_DATE 	DATETIME
    	DECLARE @END_DATE 	DATETIME
    	DECLARE @TEMP_VARCHAR 	VARCHAR(20)
    	    
   	SET @TEMP_VARCHAR = CONVERT(VARCHAR, CURRENT_TIMESTAMP, 101)+' 12:00AM'
    	SET @START_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)
    	SET @TEMP_VARCHAR = NULL
    	SET @TEMP_VARCHAR = CONVERT(VARCHAR, CURRENT_TIMESTAMP, 101)+' 11:59PM' 
    	SET @END_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)


	/*Deleting lots, if lots moving more than once*/
	DELETE FROM LOT_TABLE_HISTORY WHERE LOT_CURRENT_STATUS != 'Close' 
	AND Lot_Table_History.timestamp BETWEEN @START_DATE AND @END_DATE

	/*Inserting into LOT_TABLE_HISTORY table*/
	INSERT INTO LOT_TABLE_HISTORY(Lot_ID, Processstep_Id, Location_Id,Lot_Number,Fab_Lot_Number,Vendor_Lot_Number,Lot_Entered_By,Lot_Manual_Hold,Lot_Category,Wafer_Qty_In,Wafer_Qty_Out,Qty_In,Qty_Out,Date_In,Date_Out,Stage_Name,Stage_Position,Lot_Current_Status,Lot_Comments,[Timestamp], Lot_Cost, Lot_Cummulative_Cost, User_name, Lot_Date_Code )
	( SELECT Lot_ID, Processstep_Id, Location_Id,Lot_Number,Fab_Lot_Number,Vendor_Lot_Number,Lot_Entered_By,Lot_Manual_Hold,Lot_Category,Wafer_Qty_In,Wafer_Qty_Out,Qty_In,Qty_Out,Date_In,Date_Out,Stage_Name,Stage_Position,Lot_Current_Status,Lot_Comments,CURRENT_TIMESTAMP, Lot_Cost, Lot_Cummulative_Cost, user_name, Lot_Date_CODE
	 FROM LOT_TABLE WHERE LOT_CURRENT_STATUS != 'Close')

	/*Deleting transit lots, if lots moving more than once*/
	DELETE FROM Shipped_Lot_History WHERE  Status != 'Close' and Ship_Status = 0
	AND Shipped_Lot_History.timestamp BETWEEN @START_DATE AND @END_DATE

	/*Inserting into Shipped_Lot_History table*/
	Insert into Shipped_Lot_History(ShippedLot_ID, Lot_ID, Ship_Date, Ship_Qty, Status, Ship_Status, Ship_Req_id, Ship_Good_Die_Qty, PO_ID, SHOP_ORDER_NO, SHIP_DROP_OUT, SL_Customer_PO, SL_Invoice_Number, SL_Lead_Count, SL_Package, SL_MAWB_Number, SL_HAWB_Number, SL_ETD, SL_ETA, Wafer_Id, SL_NextLocation, SL_Flight_Number, SL_Customer_SO, WaferID_LotNo, [Timestamp], Lot_Cost, Lot_Cummulative_Cost )	
	(SELECT ShippedLot_ID, Lot_ID, Ship_Date, Ship_Qty, Status, Ship_Status, Ship_Req_id, Ship_Good_Die_Qty, PO_ID, SHOP_ORDER_NO, SHIP_DROP_OUT, SL_Customer_PO, SL_Invoice_Number, SL_Lead_Count, SL_Package, SL_MAWB_Number, SL_HAWB_Number, SL_ETD, SL_ETA, Wafer_Id, SL_NextLocation, SL_Flight_Number, SL_Customer_SO, WaferID_LotNo, CURRENT_TIMESTAMP, Lot_Cost, Lot_Cummulative_Cost from SHIPPED_LOT where Status != 'Close' and Ship_Status = 0 )

	/*Deleting RMA RECEIVED LOTS, if lots moving more than once*/
	DELETE FROM RMA_RECEIVE_LOT_HISTORY_TABLE WHERE  Status = 'Receive' AND RMA_RECEIVE_LOT_HISTORY_TABLE.timestamp BETWEEN @START_DATE AND @END_DATE

	/*Inserting into RMA_RECEIVE_LOT_HISTORY_TABLE table*/
	Insert into RMA_RECEIVE_LOT_HISTORY_TABLE(RMA_RECEIVE_LOT_ID, RMA_RECEIVE_ID, Processstep_Id, Location_Id, Lot_Number,Fab_Lot_Number, Qty_In, Date_In, Comments, [Timestamp], [Status] , DATE_CODE)	
	(SELECT RMA_RECEIVE_LOT_ID, RMA_RECEIVE_ID, Processstep_Id, Location_Id, Lot_Number,Fab_Lot_Number, Qty_In, Date_In, Comments, CURRENT_TIMESTAMP, [Status], DATE_CODE  from RMA_RECEIVE_LOT_TABLE where Status = 'Receive' )
	
end